
import pandas as pd 
import seaborn as sns 
from pandas_profiling import ProfileReport

import plotly.express as px
import numpy as np
import matplotlib.pyplot as plt
import statsmodels.api as sm
import missingno as msno

from plotly.offline import init_notebook_mode, iplot
init_notebook_mode(connected=True)


# Einlesen der CSV Datei, Seperator: #
df = pd.read_csv("data_abschlussprojekt.csv",
                       sep="#",                         # Daten ohne Fehlermeldung einlesen
                       engine="python",
                       skipinitialspace=True,           #löscht Leerzeichen nach Separator!!!!!!!!!!!!!!!!!!! 
                       index_col=0)                     #erste Spalte Index raus
df


df.columns
#df.columns = df.columns.str.strip()

Index(['funded_amount', 'loan_amount', 'activity', 'sector', 'use',
       'country_code', 'country', 'region', 'currency', 'term_in_months',
       'lender_count', 'borrower_genders', 'repayment_interval'],
      dtype='object')


# Erstellung eines Data ProfilReports --> Output Datei: dataprofile.hmtl
#prof = ProfileReport(df, minimal=True)
#prof.to_file(output_file="dataprofile.html")


df.head()


df.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 671205 entries, 0 to 671204
Data columns (total 13 columns):
 #   Column              Non-Null Count   Dtype  
---  ------              --------------   -----  
 0   funded_amount       671205 non-null  float64
 1   loan_amount         671205 non-null  float64
 2   activity            671205 non-null  object 
 3   sector              671205 non-null  object 
 4   use                 666972 non-null  object 
 5   country_code        671197 non-null  object 
 6   country             671205 non-null  object 
 7   region              614405 non-null  object 
 8   currency            671205 non-null  object 
 9   term_in_months      671205 non-null  float64
 10  lender_count        671205 non-null  int64  
 11  borrower_genders    666984 non-null  object 
 12  repayment_interval  671205 non-null  object 
dtypes: float64(3), int64(1), object(9)
memory usage: 71.7+ MB


df.describe()


df.describe(include=["O"])


df.duplicated().sum()

24588


df.loc[df.loc[:,:].duplicated(keep=False)]


# z.B. sind die Zeilen 392, 405, 498, 606 identisch

# Projekt kann mehrfach eingestellt worden sein, da mehere Solar Lampen gekauft werden müssen.
# Der gleiche Investor kann mehrfach investieren


df.loc[df[['funded_amount',"loan_amount","activity","sector","use"]].duplicated()==True,:]


df.loc[df["use"].duplicated()==False,:]


df.loc[df["use"].duplicated()==True,:]


df.use.unique()

array(['To buy seasonal, fresh fruits to sell. ',
       'to repair and maintain the auto rickshaw used in their business.',
       'To repair their old cycle-van and buy another one to rent out as a source of income',
       ...,
       'Pretend the issue with loan got addressed by Kiva Coordinator.',
       'Kiva Coordinator replaced loan use. Should see this in viewdiff.',
       'Edited loan use in english.'], dtype=object)


df_use = df.groupby(by=["use", "country"], as_index=False).agg(count_use=("use","count"))
df_use


# Löschen der Duplikate
df.drop_duplicates(inplace=True)


# Prüfung
df.loc[df.loc[:,:].duplicated(keep=False)]


# Übersicht der systemseitig erkannten Nullwerte
#import missingno as msno
# ACHTUNG speicherintensiv
msno.matrix(df, figsize=(9,6), fontsize=10)
plt.show()


df.head()


df.isnull().sum()

funded_amount             0
loan_amount               0
activity                  0
sector                    0
use                    3900
country_code              8
country                   0
region                56158
currency                  0
term_in_months            0
lender_count              0
borrower_genders       3888
repayment_interval        0
dtype: int64


#Prozentualer Anteil der Nullwerte
df.isnull().mean() * 100

funded_amount         0.000000
loan_amount           0.000000
activity              0.000000
sector                0.000000
use                   0.603139
country_code          0.001237
country               0.000000
region                8.684894
currency              0.000000
term_in_months        0.000000
lender_count          0.000000
borrower_genders      0.601283
repayment_interval    0.000000
dtype: float64


df.use.unique()

array(['To buy seasonal, fresh fruits to sell. ',
       'to repair and maintain the auto rickshaw used in their business.',
       'To repair their old cycle-van and buy another one to rent out as a source of income',
       ...,
       'Pretend the issue with loan got addressed by Kiva Coordinator.',
       'Kiva Coordinator replaced loan use. Should see this in viewdiff.',
       'Edited loan use in english.'], dtype=object)


df.loc[df.loc[:,"use"].isnull(),:]


df.country_code.unique()
# nan Werte vorhanden

array(['PK', 'IN', 'KE', 'NI', 'SV', 'TZ', 'PH', 'PE', 'SN', 'KH', 'LR',
       'VN', 'IQ', 'HN', 'PS', 'MN', 'US', 'ML', 'CO', 'TJ', 'GT', 'EC',
       'BO', 'YE', 'GH', 'SL', 'HT', 'CL', 'JO', 'UG', 'BI', 'BF', 'TL',
       'ID', 'GE', 'UA', 'XK', 'AL', 'CD', 'CR', 'SO', 'ZW', 'CM', 'TR',
       'AZ', 'DO', 'BR', 'MX', 'KG', 'AM', 'PY', 'LB', 'WS', 'IL', 'RW',
       'ZM', 'NP', 'CG', 'MZ', 'ZA', 'TG', 'BJ', 'BZ', 'SR', 'TH', 'NG',
       'MR', 'VU', 'PA', 'VI', 'VC', 'LA', 'MW', 'MM', 'MD', 'SS', 'SB',
       'CN', 'EG', 'GU', 'AF', 'MG', nan, 'PR', 'LS', 'CI', 'BT'],
      dtype=object)


df.loc[df.loc[:,"country_code"].isnull(),:]


df.region.unique()
#nan Werte vorhanden - hier nicht sichtbar

array(['Lahore', 'Maynaguri', 'Abdul Hakeem', ..., 'Gbenikoro Village',
       'Morimaraia', 'alejandria'], dtype=object)


df.loc[df.loc[:,"region"].isnull(),:]


df_region_nan = df.loc[df.loc[:,"region"].isnull(),:]

df_group_by_region_nan = df_region_nan.groupby(by=["country"], as_index=False).agg(country_region_nan_sum=("country","count"))
df_group_by_region_nan.sort_values(by=["country_region_nan_sum"])


# Test für United States
df.loc[df.loc[:,"country"]=="United States",:]


# Analyse Beispiel United States
# 6092 Einträge für United States und davon 5172 mit Region Null --> keinen Sinn NaN zu ersetzen


# Test für United States
df.loc[df.loc[:,"country"]=="El Salvador",:]


# Analyse Beispiel El Salvador

#39845 Einträge für El Salvador und davon 20222 mit Region Null --> keinen Sinn NaN zu ersetzen


df.region.isnull().sum()

56158


# Löschen der Spalte "region"
df.drop(columns=['region'], inplace=True)
df


df.borrower_genders.unique()

array(['female', 'female, female', 'female, female, female', ...,
       'female, female, male, female, female, female, female, female, female, female, male, male, female, female, male, female, female, female, female, female, female, female',
       'male, female, female, female, female, female, female, female, male, male, female, male, female, male, male, male',
       'female, female, female, male, female, female, female, male, female, female, female, male, female, male, female, female, female, female, female, female, female, female, female, female, female, female, female, female, male'],
      dtype=object)


# NaN-Werte
df.loc[df.loc[:,"borrower_genders"].isnull(),:]


# Pro Zeile können mehre borrower und damit gender vorkommen
# 2 neue Spalten zur Aufteilung der vorkommenden gender
# sex_count_female
# sex_count_male


df_new = df.copy()
df_new["borrower_genders"] = df_new["borrower_genders"].str.replace("female", "Frau")
df_new["borrower_genders"] = df_new["borrower_genders"].str.replace("male", "Mann")

df_new["sex_count_female"] = df_new["borrower_genders"].str.count("Frau")
df_new["sex_count_male"] = df_new["borrower_genders"].str.count("Mann")

df_new


# Gruppierung und country und aggregation nach gender female
df_new.groupby("country").agg(func={"sex_count_female":"sum"}).sort_values(by="sex_count_female", ascending=False)


df_gender_research = df_new.groupby("country").agg(
female_sum=("sex_count_female", "sum"),
female_mean=("sex_count_female", "mean"),
male_sum=("sex_count_male", "sum"),
male_mean=("sex_count_male", "mean"))
df_gender_research


# Ermittlung verschiedener statistischer Werte
# female_percentage gibt prozentualen Anteil der Frauen an und wird später bei der Befüllung wieder verwendet

df_gender_research["female_percentage"] = df_gender_research["female_sum"]/(df_gender_research["female_sum"]+df_gender_research["male_sum"])
df_gender_research.sort_values(by="female_percentage", ascending=False).head(60)


df_gender_research.loc[(df_gender_research["female_percentage"]>0.5),:].sort_values(by="female_percentage")


# Erstellung einer country Liste mit female_percentage > 0.5 
female_country_array = df_gender_research.loc[(df_gender_research["female_percentage"]>=0.5),].index.values
female_country_array

array(['Afghanistan', 'Albania', 'Armenia', 'Benin', 'Bhutan', 'Bolivia',
       'Brazil', 'Burkina Faso', 'Burundi', 'Cambodia', 'Cameroon',
       'Chile', 'Colombia', 'Congo', 'Costa Rica', "Cote D'Ivoire",
       'Dominican Republic', 'Ecuador', 'El Salvador', 'Georgia', 'Ghana',
       'Guam', 'Guatemala', 'Haiti', 'Honduras', 'India', 'Indonesia',
       'Iraq', 'Israel', 'Jordan', 'Kenya', 'Kyrgyzstan', 'Lebanon',
       'Lesotho', 'Liberia', 'Madagascar', 'Malawi', 'Mali', 'Mexico',
       'Moldova', 'Mozambique', 'Myanmar (Burma)', 'Namibia', 'Nepal',
       'Nicaragua', 'Pakistan', 'Panama', 'Paraguay', 'Peru',
       'Philippines', 'Puerto Rico', 'Rwanda',
       'Saint Vincent and the Grenadines', 'Samoa', 'Senegal',
       'Sierra Leone', 'Solomon Islands', 'Somalia', 'South Africa',
       'South Sudan', 'Tajikistan', 'Tanzania', 'Thailand',
       'The Democratic Republic of the Congo', 'Timor-Leste', 'Togo',
       'Turkey', 'Ukraine', 'United States', 'Vietnam', 'Virgin Islands',
       'Yemen', 'Zimbabwe'], dtype=object)


df.loc[(df["borrower_genders"].isna()) & ~(df["country"].isin(female_country_array)), "borrower_genders"]="male"
df.loc[(df["borrower_genders"].isna()) & (df["country"].isin(female_country_array)), "borrower_genders"]="female"


df["borrower_genders"].isna()

0         False
1         False
2         False
3         False
4         False
          ...  
671179    False
671181    False
671182    False
671184    False
671188    False
Name: borrower_genders, Length: 646617, dtype: bool


df.use.isnull().sum()

3900


df.loc[df.loc[:,"use"].isnull(),:]


# Löschen der Spalte "use"
df.drop(columns=['use'], inplace=True)
df


df.country_code.isnull().sum()

8


df.loc[df.loc[:,"country"]=="Namibia",:]
# alle NaN Werte in Spalte country_code betreffen das Land Namibia


# Spalte country_code
# Country: Namibia --> Alpha-3 code NAM --> Spalte country_code: "NAM"

df.loc[:,"country_code"].replace(np.nan,"NAM" , inplace=True)
df.loc[df.loc[:,"country"]=="Namibia",:]


df.country_code.isnull().sum()

0


df.dtypes

funded_amount         float64
loan_amount           float64
activity               object
sector                 object
country_code           object
country                object
currency               object
term_in_months        float64
lender_count            int64
borrower_genders       object
repayment_interval     object
dtype: object


# Spalten mit Zahlenwerten sind entweder int oder float --> gut


df.activity.unique()
# keine Auffälligkeiten bzgl. Null Werten bzw. Synonyme für Null Werte

array(['Fruits & Vegetables', 'Rickshaw', 'Transportation', 'Embroidery',
       'Milk Sales', 'Services', 'Dairy', 'Beauty Salon', 'Manufacturing',
       'Food Production/Sales', 'Wholesale', 'General Store',
       'Clothing Sales', 'Poultry', 'Tailoring', 'Sewing', 'Bakery',
       'Restaurant', 'Food Stall', 'Farming', 'Construction Supplies',
       'Personal Products Sales', 'Home Products Sales',
       'Natural Medicines', 'Fish Selling', 'Education provider',
       'Shoe Sales', 'Machinery Rental', 'Butcher Shop', 'Pigs',
       'Personal Expenses', 'Food Market', 'Cosmetics Sales',
       'Personal Housing Expenses', 'Retail', 'Energy', 'Grocery Store',
       'Construction', 'Agriculture', 'Motorcycle Transport',
       'Charcoal Sales', 'Food', 'Pharmacy', 'Fishing', 'Timber Sales',
       'Cattle', 'Electronics Repair', 'Electronics Sales', 'Vehicle',
       'Cafe', 'Blacksmith', 'Higher education costs', 'Used Clothing',
       'Fuel/Firewood', 'Upholstery', 'Catering', 'Animal Sales',
       'Cereals', 'Vehicle Repairs', 'Arts',
       'Cloth & Dressmaking Supplies', 'Mobile Phones', 'Spare Parts',
       'Clothing', 'Metal Shop', 'Barber Shop', 'Furniture Making',
       'Crafts', 'Home Energy', 'Home Appliances', 'Wedding Expenses',
       'Taxi', 'Secretarial Services', 'Livestock', 'Property',
       'Recycling', 'Farm Supplies', 'Auto Repair', 'Beverages',
       'Plastics Sales', 'Electrical Goods', 'Carpentry', 'Photography',
       'Jewelry', 'Bricks', 'Pub', 'Phone Use Sales',
       'Water Distribution', 'Paper Sales', 'Computers',
       'Liquor Store / Off-License', 'Utilities', 'Knitting', 'Weaving',
       'Party Supplies', 'Medical Clinic', 'Internet Cafe',
       'Consumer Goods', 'Cement', 'Electrician',
       'Primary/secondary school costs', 'Veterinary Sales',
       'Land Rental', 'Laundry', 'Call Center', 'Perfumes', 'Hotel',
       'Motorcycle Repair', 'Movie Tapes & DVDs', 'Quarrying',
       'Personal Medical Expenses', 'Bookstore', 'Decorations Sales',
       'Recycled Materials', 'Office Supplies', 'Souvenir Sales',
       'Renewable Energy Products', 'Health', 'Printing', 'Phone Repair',
       'Traveling Sales', 'Flowers', 'Bicycle Repair', 'Entertainment',
       'Phone Accessories', 'Hardware', 'Used Shoes',
       'Music Discs & Tapes', 'Games', 'Balut-Making', 'Textiles',
       'Child Care', 'Goods Distribution', 'Florist', 'Cobbler', 'Dental',
       'Bookbinding', 'Cheese Making', 'Bicycle Sales', 'Well digging',
       'Technology', 'Musical Performance', 'Waste Management', 'Film',
       'Tourism', 'Musical Instruments', 'Religious Articles',
       'Machine Shop', 'Cleaning Services', 'Sporting Good Sales',
       'Patchwork', 'Funerals', 'Air Conditioning', 'Communications',
       'Adult Care', 'Landscaping / Gardening', 'Aquaculture',
       'Beekeeping', 'Event Planning', 'Celebrations', 'Computer',
       'Personal Care Products', 'Mobile Transactions'], dtype=object)


df.country.unique()
# keine Auffälligkeiten bzgl. Null Werten bzw. Synonyme für Null Werte

array(['Pakistan', 'India', 'Kenya', 'Nicaragua', 'El Salvador',
       'Tanzania', 'Philippines', 'Peru', 'Senegal', 'Cambodia',
       'Liberia', 'Vietnam', 'Iraq', 'Honduras', 'Palestine', 'Mongolia',
       'United States', 'Mali', 'Colombia', 'Tajikistan', 'Guatemala',
       'Ecuador', 'Bolivia', 'Yemen', 'Ghana', 'Sierra Leone', 'Haiti',
       'Chile', 'Jordan', 'Uganda', 'Burundi', 'Burkina Faso',
       'Timor-Leste', 'Indonesia', 'Georgia', 'Ukraine', 'Kosovo',
       'Albania', 'The Democratic Republic of the Congo', 'Costa Rica',
       'Somalia', 'Zimbabwe', 'Cameroon', 'Turkey', 'Azerbaijan',
       'Dominican Republic', 'Brazil', 'Mexico', 'Kyrgyzstan', 'Armenia',
       'Paraguay', 'Lebanon', 'Samoa', 'Israel', 'Rwanda', 'Zambia',
       'Nepal', 'Congo', 'Mozambique', 'South Africa', 'Togo', 'Benin',
       'Belize', 'Suriname', 'Thailand', 'Nigeria', 'Mauritania',
       'Vanuatu', 'Panama', 'Virgin Islands',
       'Saint Vincent and the Grenadines',
       "Lao People's Democratic Republic", 'Malawi', 'Myanmar (Burma)',
       'Moldova', 'South Sudan', 'Solomon Islands', 'China', 'Egypt',
       'Guam', 'Afghanistan', 'Madagascar', 'Namibia', 'Puerto Rico',
       'Lesotho', "Cote D'Ivoire", 'Bhutan'], dtype=object)


df.sector.unique()
# keine Auffälligkeiten bzgl. Null Werten bzw. Synonyme für Null Werte

array(['Food', 'Transportation', 'Arts', 'Services', 'Agriculture',
       'Manufacturing', 'Wholesale', 'Retail', 'Clothing', 'Construction',
       'Health', 'Education', 'Personal Use', 'Housing', 'Entertainment'],
      dtype=object)


df.currency.unique()
# keine Auffälligkeiten bzgl. Null Werten bzw. Synonyme für Null Werte

array(['PKR', 'INR', 'KES', 'NIO', 'USD', 'TZS', 'PHP', 'PEN', 'XOF',
       'LRD', 'VND', 'HNL', 'MNT', 'COP', 'GTQ', 'TJS', 'BOB', 'YER',
       'KHR', 'GHS', 'SLL', 'HTG', 'CLP', 'JOD', 'UGX', 'BIF', 'IDR',
       'GEL', 'UAH', 'EUR', 'ALL', 'CRC', 'XAF', 'TRY', 'AZN', 'DOP',
       'BRL', 'MXN', 'KGS', 'AMD', 'PYG', 'LBP', 'WST', 'ILS', 'RWF',
       'ZMW', 'NPR', 'MZN', 'ZAR', 'BZD', 'SRD', 'NGN', 'VUV', 'XCD',
       'MWK', 'LAK', 'MMK', 'ZWD', 'MDL', 'SSP', 'SBD', 'CNY', 'EGP',
       'MGA', 'NAD', 'LSL', 'THB'], dtype=object)


df.repayment_interval.unique()
# keine Auffälligkeiten bzgl. Null Werten bzw. Synonyme für Null Werte

array(['irregular', 'bullet', 'monthly', 'weekly'], dtype=object)


# Definition von Synonymen für Missing values und Suche danach....
df.isin(["?","unknown","-"]).sum() 
# keine Auffälligkeiten bzgl. Null Werten bzw. Synonyme für Null Werte

funded_amount         0
loan_amount           0
activity              0
sector                0
country_code          0
country               0
currency              0
term_in_months        0
lender_count          0
borrower_genders      0
repayment_interval    0
dtype: int64


# weitere Prüfung:
# funded_amount = 0 --> lender_count = 0
df.loc[df.loc[:,"funded_amount"]==0,:]


# Abschließenden Prüfung auf systemseitige Nullwerte
df.describe(include=["O"])


## Abschließenden Prüfung auf systemseitige Nullwerte
#msno.matrix(df, figsize=(9,6), fontsize=10)
#plt.show()


# Abschließende Prüfung auf neue Duplikate
df.loc[df.loc[:,:].duplicated(keep=False)]

# Durch die Behandlung der Nullwerte NaN entstehen neue Duplikate die allerdings keine sind, da sie vor der Nullwert Behandlung verschieden waren.


df.describe()


df.loc[df["funded_amount"]==100000,:]

# Keine Auffälligkeiten bzgl. Extremwerte bzw. Ausreißer


df['borrower_genders'].value_counts()

female                                                                                                                                                                                                                          418768
male                                                                                                                                                                                                                            129998
female, female, female                                                                                                                                                                                                           10215
female, female                                                                                                                                                                                                                   10040
female, female, female, female                                                                                                                                                                                                    7868
                                                                                                                                                                                                                                 ...  
male, female, female, female, male, female, female, female, male, female, female, male, female, female, female, female, female                                                                                                       1
female, female, male, female, male, female, female, male, female, female, female, female, female, female, female, female, female, female                                                                                             1
male, female, female, male, female, female, female, male, female, male, female, female, female                                                                                                                                       1
female, female, female, female, female, female, female, female, female, female, female, male, female, female, female, female, female, female, female, female, female, female, female                                                 1
female, female, female, male, female, female, female, male, female, female, female, male, female, male, female, female, female, female, female, female, female, female, female, female, female, female, female, female, male         1
Name: borrower_genders, Length: 11298, dtype: int64


# Funktion um die verschiedenen Einträge in der Spalte borrower_genders zu kategorisieren
def categorize_genders(borrower_genders):
    gender_list = borrower_genders.split(",")
    gender_list = list(set(gender_list))
    gender_list = [borrower_genders.strip() for borrower_genders in gender_list]
    if len(gender_list)==2:
        if 'female' in gender_list and 'male' in gender_list:
            return 'both'
        elif 'female' in gender_list:
            return 'multiple female'
        elif 'male' in gender_list:
            return 'multiple male'
    elif gender_list[0]=='female':
        return 'female'
    elif gender_list[0]=='male':
        return 'male'
    else:
        return 'unknown'


# neue Spalte "gender" mit den neuen Einträgen
#'female', 'multiple female', 'male', 'multiple male', 'both','unknown'


df['gender'] = df['borrower_genders'].apply(categorize_genders)


df.gender.unique()

array(['female', 'multiple female', 'male', 'multiple male', 'both'],
      dtype=object)

df


# Importing the iso_country codes to plot the choropleth graphs
df_country_codes = pd.read_csv('wikipedia-iso-country-codes.csv')

#renameing the column names
df_country_codes.columns = ['country','Alpha-2 code','Alpha-3_code','Numeric code', 'ISO 3166-2']
df_country_codes

df_country_codes.drop(columns=["Alpha-2 code","Numeric code","ISO 3166-2"], inplace=True)
df_country_codes


#combining the two data frames to get the country codes
df_country_merge = pd.merge(df_country_codes, df, on='country', how='right')
df_country_merge.columns = ["country","Alpha-3_code","funded_amount","loan_amount","activity","sector","country_code","currency",
                            "term_in_months","lender_count","borrower_genders","repayment_interval",
                            "gender"]

df=df_country_merge
df


df['success_rate'] = round((df["funded_amount"]/df["loan_amount"])*100)
df

df


df.head()


# Gruppierung nach country, Zählung der funded_amount = Anzahl Projekte und Sortierung
#df_country_anz_projekte = df.groupby(["country"], as_index = False).agg({"funded_amount":"count"})

df_group_agg = df.groupby(["country"], as_index = False).agg(projects_count=("funded_amount","count"),
                                                                        funded_amount_sum=("funded_amount","sum"),
                                                                        loan_amount_sum=("loan_amount","sum"))
                                                                        
df_group_agg.sort_values(by=["projects_count"], ascending=False, inplace=True)
#df_country_anz_projekte.rename(columns={"funded_amount":"projects"}, inplace=True)
df_group_agg


#combining the two data frames to get the country codes

df_group_agg = pd.merge(df_country_codes,df_group_agg, on='country', how='right')
df_group_agg.columns = ["country","Alpha-3_code","projects_count","funded_amount_sum","loan_amount_sum"]
df_group_agg


fig = px.choropleth(df_group_agg, 
                    locations="Alpha-3_code",
                    color="projects_count",                                       
                    hover_name="country", # column to add to hover information
                    color_continuous_scale=px.colors.sequential.tempo,
                    labels="Anzahl an Kredit Anfragen nach Land" )

fig.update_layout(
    title_text = 'Plot1 - Anzahl der Projekte je Land (Anzahl Kreditanfragen)')
fig.show()


fig = px.choropleth(df_group_agg, locations="Alpha-3_code",
                   color="loan_amount_sum", 
                   #color="funded_amount_sum",
                   hover_name="country", 
                   color_continuous_scale=px.colors.sequential.tempo)


fig.update_layout(
    title_text = 'Summe der Zielbeträge der gewünschten Darlehen nach Land')

fig.show()


df_group_agg['success_rate_sum'] = round((df_group_agg["funded_amount_sum"]/df_group_agg["loan_amount_sum"])*100)
df_group_agg


fig = px.choropleth(df_group_agg, locations="Alpha-3_code",
                   color="success_rate_sum", 
                   hover_name="country", 
                   color_continuous_scale=px.colors.sequential.tempo)


fig.update_layout(
    title_text = 'Success Rate aller Projekte nach Land')

fig.show()


df_group_agg.head()


fig_fund_amount = px.bar(df_group_agg.sort_values(by="success_rate_sum", ascending=True),
                         x="country",
                         y="success_rate_sum",
                         hover_data=["success_rate_sum"],
                         title="Plot 2 - Success Rate aller Projekte nach Länder",
                         labels={"country":"Länder"},
                         color="projects_count",
                         width=1000,
                         height=1000
                        
                        )

fig_fund_amount.show()

df


df_group_agg_country_gender = df.groupby(["country","gender"], as_index = False).agg(projects_count=("funded_amount","count"),
                                                                        funded_amount_sum=("funded_amount","sum"),
                                                                        loan_amount_sum=("loan_amount","sum"),
                                                                        lender_count_sum=("lender_count","sum"))


df_group_agg_country_gender['rate_of_success'] = round((df_group_agg_country_gender['funded_amount_sum']/
                                                        df_group_agg_country_gender['loan_amount_sum'])*100)


df_group_agg_country_gender


fig = px.sunburst(df_group_agg_country_gender, 
                  path=['gender', 'country'], 
                  values='funded_amount_sum', 
                  #color='rate_of_success',
                  #color_continuous_scale='RdBu',
                  width =800,
                  height =800,
                  title="Gender und Land Abhängigkeit der Projekt- Owner zu den erhaltenen Kredit Beträgen"
                 )
fig.update_traces(textinfo="label+percent parent")                                
                
fig.show()


df_group_agg_country_gender_activity_sector = df.groupby(["country","gender","activity","sector"], as_index = False).agg(projects_count=("funded_amount","count"),
                                                                        funded_amount_sum=("funded_amount","sum"),
                                                                        loan_amount_sum=("loan_amount","sum"),
                                                                        lender_count_sum=("lender_count","sum"))
df_group_agg_country_gender_activity_sector


df_group_agg_country_gender_activity_sector['rate_of_success'] = round((df_group_agg_country_gender_activity_sector['funded_amount_sum']/
       df_group_agg_country_gender_activity_sector['loan_amount_sum'])*100)


df_group_agg_country_gender_activity_sector = pd.merge(df_country_codes,df_group_agg_country_gender_activity_sector, on='country', how='right')
df_group_agg_country_gender_activity_sector.columns = ["country","Alpha-3_code","gender","activity","sector",
                                                      "projects_count","funded_amount_sum","loan_amount_sum","lender_count_sum","rate_of_success"]
df_group_agg_country_gender_activity_sector


# customizing!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


fig = px.sunburst(df_group_agg_country_gender_activity_sector, 
                  path=['gender','sector'], 
                  values='funded_amount_sum', 
                   #values='funded_amount_sum',
                  width =1000,
                  height =1000,
                  title="Plot 3 - Erhaltener Kreditbetrag nach Geschlecht, Sector und farbliche Darstellung der Success Rate",
                  color='rate_of_success', 
                  hover_data=['sector'],
                  #color_continuous_scale='brg',
                  color_continuous_scale='RdBu',
                  
                  )
fig.update_traces(textinfo="label+percent parent")                                
                
fig.show()


# benötigte Bibliotheken importieren
## für die Dash-App

from jupyter_dash import JupyterDash
from dash import dcc
#import dash_core_components as dcc 

from dash import html
#import dash_html_components as html 

from dash.dependencies import Input, Output


df_selection =df_group_agg_country_sector.loc[df_group_agg_country_sector["country"].isin(["Kenya","United States"]),:]
df_selection

df_selection


df_group_agg_country_sector.loc[df_group_agg_country_sector["country"]


df_selecion_dash = df_group_agg_country_sector.loc[df_group_agg_country_sector.loc[:,"sector"]=="Agriculture",:]
df_selecion_dash


df_group = df_selecion_dash.groupby(by=["country"], as_index=False).agg(projects_count=("funded_amount_sum","sum"))
df_group


df_group = df_select_disease.groupby(by=["State"], as_index=False).agg(ber_mean=("in Thousand of bees Impacted","mean"))


fig = px.bar(df_selection.sort_values(by="projects_count", ascending=False),
                         x="sector",
                         y="projects_count",
                         hover_data=["country"],
                         title="United States - Sectoren nach Success Rate",
                         labels=("sector"),
                         color="rate_of_success",
                         color_continuous_scale="YlGnBu",
                         width=1000,
                         height=1000
                        
                        )

fig.show()


# Vorlage - Grundstruktur

# 1. App erstellen

my_app = JupyterDash(__name__)


# 2. Layout festlegen

my_app.layout = html.Div(children=[
                             # Überschrift der App
    
                            html.H1(children="Projektsumme nach Land im Bereich Agriculture",
                                    style={"textAlign": "center"}),
    
                            # Dropdown
    
                            dcc.Dropdown(id="graph_drop",
                                         options=sorted(df_group_agg_country_sector.loc[:,"country"].unique()),
                                         style={"width":250},
                                         placeholder="Please select a year"
                            
                                        ),
    
                            dcc.Graph(
                                      id="the_graph",
                                      figure={}
                                        )
                           

                            ])

# 3. Callback erstellen 

@my_app.callback( 
                        # 1. IMMER die Outputs
                       Output(component_id="the_graph",     # id vom Graphobjekt
                              component_property="figure"), # Wohin sollen die Werte übgergeben werden --> in die Figur, da diese angepasst wird nachdem die Daten geändert wurden
                      
                        # 2. IMMER die Inputs
                        Input(component_id="graph_drop",    # in welchem Objekt werden Werte geändert?
                              component_property="value"),  # Welches property/parameter wird geändert?
                        )

# 3.1 Aufbau des Callbacks: Verbindung mit Elementen aus dem Layout


def update_plot(selected_year):
    
    df_select2 = df_group_agg_country_sector.loc[df_group_agg_country_sector.loc[:,"country"]==selected_year,:]
    
    
    #df_select = df_import.loc[df_import.loc[:,"Year"]==selected_year,:]


    df_selecion_dash = df_select2.loc[df_select2.loc[:,"sector"]=="Agriculture",:]
    df_selecion_dash

    #df_select_disease = df_select.loc[df_select.loc[:,"Affected by"]=="Disease",:]
    #df_select_disease

    df_group = df_selecion_dash.groupby(by=["country"], as_index=False).agg(projects_count=("funded_amount_sum","sum"))
    df_group   


    #df_group = df_select_disease.groupby(by=["State"], as_index=False).agg(ber_mean=("in Thousand of bees Impacted","mean"))
    #df_group

# 3.2 Erstellen der Update-Funktion: ändert sich der Input, ändert sich der Output

    my_bar = px.bar(data_frame=df_group ,
                            x="country",
                            y="projects_count"
                    )
    
    return my_bar # Rückgabewert ist ein dictionary, welches an Output component_property übergeben wird





# 4. App starten

my_app.run_server(mode='inline')


fig = px.sunburst(df_group_agg_country_gender_activity_sector, 
                  path=['gender', 'activity'], 
                  values='projects_count', 
                  width =1000,
                  height =1000,
                  title="Anzahl Projekte nach Sector, Geschlecht und Success-Rate",
                  color='rate_of_success', 
                  hover_data=['activity'],
                  #color_continuous_scale='brg',
                  color_continuous_scale='RdBu',
                  #color_continuous_midpoint=np.average(df_group_agg_country_gender_activity_sector['rate_of_success'],
                  #                                    weights=df_group_agg_country_gender_activity_sector['funded_amount_sum'])
                  )
                                 
fig.update_traces(textinfo="label+percent parent")           
fig.show()


df_group_agg_country_sector = df.groupby(["country","sector"], as_index = False).agg(projects_count=("funded_amount","count"),
                                                                        funded_amount_sum=("funded_amount","sum"),
                                                                        loan_amount_sum=("loan_amount","sum"),
                                                                        lender_count_sum=("lender_count","sum"))


df_group_agg_country_sector['rate_of_success'] = round((df_group_agg_country_sector['funded_amount_sum']/
       df_group_agg_country_sector['loan_amount_sum'])*100)


df_group_agg_country_sector = pd.merge(df_country_codes,df_group_agg_country_sector, on='country', how='right')
df_group_agg_country_sector.columns = ["country","Alpha-3_code","sector",
                                                      "projects_count","funded_amount_sum","loan_amount_sum","lender_count_sum","rate_of_success"]


# Hover Info erweitern

fig = px.bar(df_group_agg_country_sector.sort_values(by="rate_of_success", ascending=False),
                         x="sector",
                         y="projects_count",
                         hover_data=["country"],
                         title="Anzahl der Projekte nach Sector und Länder und Success Rate",
                         labels=("sector"),
                         color="country",
                         #color_category_sequence[Light24,
                         width=1000,
                         height=1200
                        
                        )

fig.show()


fig = px.scatter_geo(df_group_agg_country_sector.sort_values(by="rate_of_success", ascending=True), 
                     locations="Alpha-3_code",
                     color="sector", # which column to use to set the color of markers
                     color_continuous_scale="YlGnBu",
                     #color_discrete_sequence="Light24", 
                     #colors.qualitative.colour_sequence_name[Light24],
                     hover_name="country", # column added to hover information
                     size="projects_count", # size of markers
                     projection="natural earth",
                     title="Anzahl der Projekte nach Sector und Länder und Success Rate", 
                     labels="labels",
                     size_max=40,
                     width=1000, 
                     height=800, 
                    )
fig.show()


df_selection =df_group_agg_country_sector.loc[df_group_agg_country_sector["country"].isin(["United States"]),:]
df_selection

df_selection


fig = px.bar(df_selection.sort_values(by="projects_count", ascending=False),
                         x="sector",
                         y="projects_count",
                         hover_data=["country"],
                         title="United States - Sectoren nach Success Rate",
                         labels=("sector"),
                         color="rate_of_success",
                         color_continuous_scale="YlGnBu",
                         width=1000,
                         height=1000
                        
                        )

fig.show()


#df = px.data.iris()
fig = px.scatter_matrix(df_group_agg_country_gender_activity_sector,
      width=1200,
      height=1200)                  
fig.show()


df_group_agg_country_gender_activity_sector_success=df_group_agg_country_gender_activity_sector.loc[df_group_agg_country_gender_activity_sector["rate_of_success"]<50,:]


df_group_agg_country_gender_activity_sector_success


fig = px.bar(df_group_agg_country_gender_activity_sector_success.sort_values(by="rate_of_success", ascending=True),
                         x="sector",
                         y="projects_count",
                         hover_data=["country"],
                         title="Success Rate < 50% aller Projekte nach Länder",
                         labels=("sector"),
                         color="country",
                         color_continuous_scale="YlGnBu",
                         width=1000,
                         height=1000
                        
                        )

fig.show()


fig = px.bar(df_group_agg_country_gender_activity_sector, 
             x="country", 
             y="projects_count",
             color='sector', 
             barmode='group',
             height=800,
             width=1500)
fig.show()


fig = px.scatter(df_group_agg_country_gender_activity_sector, 
                 y="projects_count", 
                 x="country", 
                 color="sector")
#fig.update_traces(marker_size=10)
#fig.update_layout(scattermode="group")
fig.show()


fig = px.sunburst(df_group_agg_country_gender_activity_sector, 
                  path=['gender', 'sector'], 
                  values='lender_count_sum', 
                  width =1000,
                  height =1000,
                  title="Rate of Success, Summe Darlehensgeber nach Geschlecht und sector",
                  color='rate_of_success', 
                  hover_data=['sector'],
                  #color_continuous_scale='brg',
                  color_continuous_scale='RdBu',
                  #color_continuous_midpoint=np.average(df_group_agg_country_gender_activity_sector['rate_of_success'],
                  #                                    weights=df_group_agg_country_gender_activity_sector['funded_amount_sum'])
                  )
                                 
fig.update_traces(textinfo="label+percent parent")                  
fig.show()


df_group_agg_country_gender_activity_sector_repayment = df.groupby(["country","gender","activity","sector","repayment_interval"], as_index = False).agg(projects_count=("funded_amount","count"),
                                                                        funded_amount_sum=("funded_amount","sum"),
                                                                        loan_amount_sum=("loan_amount","sum"),
                                                                        lender_count_sum=("lender_count","sum"))
df_group_agg_country_gender_activity_sector_repayment


df_group_agg_country_gender_activity_sector_repayment['rate_of_success'] = round((df_group_agg_country_gender_activity_sector_repayment['funded_amount_sum']/
df_group_agg_country_gender_activity_sector_repayment['loan_amount_sum'])*100)


#fig = go.Figure()
fig = px.sunburst(df_group_agg_country_gender_activity_sector_repayment, 
                  path=['gender', 'repayment_interval'], 
                  values='lender_count_sum', 
                  width =1000,
                  height =1000,
                  title="Rückzahlungsrate nach Geschlecht und Success Rate",
                  color='rate_of_success', 
                  hover_data=['sector'],
                  #color_continuous_scale='brg',
                  color_continuous_scale='RdBu',
                 
                  )
                                 
                
fig.show()


df_group_agg_sector_country_gender= df.groupby(["sector","country","gender"], as_index = False).agg(projects_count=("funded_amount","count"),
                                                                        funded_amount_sum=("funded_amount","sum"),
                                                                        loan_amount_sum=("loan_amount","sum"),
                                                                        lender_count_sum=("lender_count","sum"))
df_group_agg_sector_country_gender


df_group_agg_sector_country_gender['rate_of_success'] = round((df_group_agg_sector_country_gender['funded_amount_sum']/
df_group_agg_sector_country_gender['loan_amount_sum'])*100)
df_group_agg_sector_country_gender


#Boxplots of the loan amount in sectors.
fig3= px.box(df, 
       y = 'loan_amount', 
       x = 'sector', 
       height =1200, 
       color = 'sector')

fig3.update_yaxes (range = (0,30000), constrain= 'domain')
fig3.show ()

	funded_amount	loan_amount	term_in_months	lender_count
count	671205.000000	671205.000000	671205.000000	671205.000000
mean	785.995061	842.397107	13.739022	20.590922
std	1130.398941	1198.660073	8.598919	28.459551
min	0.000000	25.000000	1.000000	0.000000
25%	250.000000	275.000000	8.000000	7.000000
50%	450.000000	500.000000	13.000000	13.000000
75%	900.000000	1000.000000	14.000000	24.000000
max	100000.000000	100000.000000	158.000000	2986.000000

	female_sum	female_mean	male_sum	male_mean
country
Afghanistan	2.0	1.000000	0.0	0.000000
Albania	1018.0	0.531038	899.0	0.468962
Armenia	5382.0	0.623928	3244.0	0.376072
Azerbaijan	703.0	0.369611	1204.0	0.633018
Belize	11.0	0.090164	111.0	0.909836
...	...	...	...	...
Vietnam	25728.0	2.699685	1587.0	0.166527
Virgin Islands	2.0	1.000000	0.0	0.000000
Yemen	4329.0	1.875650	1136.0	0.492201
Zambia	437.0	0.585007	517.0	0.692102
Zimbabwe	6772.0	1.726670	179.0	0.045640

	female_sum	female_mean	male_sum	male_mean	female_percentage
country
Afghanistan	2.0	1.000000	0.0	0.000000	1.000000
Cote D'Ivoire	1.0	1.000000	0.0	0.000000	1.000000
Virgin Islands	2.0	1.000000	0.0	0.000000	1.000000
Turkey	1686.0	1.000000	0.0	0.000000	1.000000
Solomon Islands	632.0	1.140794	0.0	0.000000	1.000000
Bhutan	1.0	1.000000	0.0	0.000000	1.000000
Guam	1.0	1.000000	0.0	0.000000	1.000000
India	27165.0	2.482409	252.0	0.023028	0.990809
Samoa	7293.0	0.990762	68.0	0.009238	0.990762
Nepal	619.0	0.990400	6.0	0.009600	0.990400
Malawi	12143.0	10.178541	148.0	0.124057	0.987959
Senegal	31904.0	9.798526	625.0	0.191953	0.980786
Paraguay	116608.0	9.871995	2523.0	0.213596	0.978822
Benin	2295.0	4.617706	50.0	0.100604	0.978678
Zimbabwe	6772.0	1.726670	179.0	0.045640	0.974248
Liberia	3499.0	0.973838	103.0	0.028667	0.971405
Israel	175.0	0.961538	7.0	0.038462	0.961538
Pakistan	26430.0	0.999282	1260.0	0.047639	0.954496
Togo	5691.0	0.991463	308.0	0.053659	0.948658
Philippines	145877.0	0.945816	8357.0	0.054184	0.945816
Vietnam	25728.0	2.699685	1587.0	0.166527	0.941900
Kyrgyzstan	5242.0	0.909438	522.0	0.090562	0.909438
Ghana	14190.0	3.359375	1462.0	0.346117	0.906593
The Democratic Republic of the Congo	40893.0	13.320195	5084.0	1.656026	0.889423
Guatemala	21472.0	2.968616	2672.0	0.369418	0.889331
Chile	120.0	13.333333	16.0	1.777778	0.882353
Timor-Leste	2351.0	0.881184	345.0	0.129310	0.872033
Sierra Leone	10094.0	1.912105	1548.0	0.293237	0.867033
Indonesia	11727.0	1.926565	1822.0	0.299326	0.865525
Dominican Republic	2264.0	4.736402	375.0	0.784519	0.857901
Lesotho	3081.0	7.300948	645.0	1.528436	0.826892
Bolivia	23078.0	2.645043	4949.0	0.567221	0.823420
Mali	38001.0	5.737732	8712.0	1.315416	0.813499
Cambodia	55204.0	2.049755	12725.0	0.472486	0.812672
Yemen	4329.0	1.875650	1136.0	0.492201	0.792132
Haiti	3464.0	1.034339	930.0	0.277695	0.788348
Lebanon	8765.0	1.012943	2384.0	0.275511	0.786169
Madagascar	2989.0	0.783486	826.0	0.216514	0.783486
Brazil	651.0	2.679012	187.0	0.769547	0.776850
Nicaragua	14479.0	1.240703	4379.0	0.375236	0.767791
Ecuador	13013.0	0.967941	3960.0	0.294555	0.766688
Burkina Faso	8693.0	3.545269	2865.0	1.168434	0.752120
Tajikistan	14514.0	0.744460	4991.0	0.256001	0.744117
Mexico	11990.0	2.150673	4309.0	0.772915	0.735628
Congo	910.0	7.109375	345.0	2.695312	0.725100
Peru	45759.0	2.064192	17356.0	0.782930	0.725010
Rwanda	36331.0	5.636209	15217.0	2.360689	0.704799
Tanzania	10456.0	2.055435	4543.0	0.893061	0.697113
Costa Rica	1506.0	0.974127	662.0	0.428202	0.694649
Burundi	11697.0	14.178182	5874.0	7.120000	0.665699
Kenya	106126.0	1.454438	54660.0	0.749106	0.660045
Myanmar (Burma)	4623.0	2.480150	2488.0	1.334764	0.650120
Jordan	2719.0	0.657716	1465.0	0.354378	0.649857
Georgia	1510.0	0.627076	898.0	0.372924	0.627076
Iraq	986.0	1.000000	594.0	0.602434	0.624051
Armenia	5382.0	0.623928	3244.0	0.376072	0.623928
South Africa	219.0	0.625714	133.0	0.380000	0.622159
Ukraine	594.0	0.616822	369.0	0.383178	0.616822
Colombia	12833.0	0.605559	8359.0	0.394441	0.605559
Thailand	102.0	0.600000	68.0	0.400000	0.600000

	female_sum	female_mean	male_sum	male_mean	female_percentage
country
Mozambique	1797.0	0.520568	1752.0	0.507532	0.506340
Puerto Rico	35.0	0.522388	32.0	0.477612	0.522388
Albania	1018.0	0.531038	899.0	0.468962	0.531038
Somalia	171.0	2.375000	148.0	2.055556	0.536050
Moldova	187.0	0.540462	159.0	0.459538	0.540462
...	...	...	...	...	...
Cote D'Ivoire	1.0	1.000000	0.0	0.000000	1.000000
Bhutan	1.0	1.000000	0.0	0.000000	1.000000
Virgin Islands	2.0	1.000000	0.0	0.000000	1.000000
Solomon Islands	632.0	1.140794	0.0	0.000000	1.000000
Afghanistan	2.0	1.000000	0.0	0.000000	1.000000

	funded_amount	loan_amount	term_in_months	lender_count
count	646617.000000	646617.000000	646617.000000	646617.000000
mean	808.045242	866.458816	13.719826	21.115360
std	1145.419041	1214.276106	8.479912	28.840674
min	0.000000	25.000000	1.000000	0.000000
25%	275.000000	275.000000	8.000000	7.000000
50%	475.000000	500.000000	13.000000	13.000000
75%	925.000000	1000.000000	14.000000	25.000000
max	100000.000000	100000.000000	158.000000	2986.000000

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
0	300.0	300.0	Fruits & Vegetables	Food	To buy seasonal, fresh fruits to sell.	PK	Pakistan	Lahore	PKR	12.0	12	female	irregular
1	575.0	575.0	Rickshaw	Transportation	to repair and maintain the auto rickshaw used ...	PK	Pakistan	Lahore	PKR	11.0	14	female, female	irregular
2	150.0	150.0	Transportation	Transportation	To repair their old cycle-van and buy another ...	IN	India	Maynaguri	INR	43.0	6	female	bullet
3	200.0	200.0	Embroidery	Arts	to purchase an embroidery machine and a variet...	PK	Pakistan	Lahore	PKR	11.0	8	female	irregular
4	400.0	400.0	Milk Sales	Food	to purchase one buffalo.	PK	Pakistan	Abdul Hakeem	PKR	14.0	16	female	monthly
...	...	...	...	...	...	...	...	...	...	...	...	...	...
671200	0.0	25.0	Livestock	Agriculture	[True, u'para compara: cemento, arenya y ladri...	PY	Paraguay	Concepción	USD	13.0	0	female	monthly
671201	25.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	1	female	monthly
671202	0.0	25.0	Games	Entertainment	NaN	KE	Kenya	NaN	KES	13.0	0	NaN	monthly
671203	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly
671204	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly

	activity	sector	use	country_code	country	region	currency	borrower_genders	repayment_interval
count	671205	671205	666972	671197	671205	614405	671205	666984	671205
unique	163	15	423452	86	87	12695	67	11298	4
top	Farming	Agriculture	to buy a water filter to provide safe drinking...	PH	Philippines	Kaduna	PHP	female	monthly
freq	72955	180302	5217	160441	160441	10000	160440	426502	342717

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
327	275.0	275.0	Farming	Agriculture	to buy fertilizers and other farm supplies.	PH	Philippines	Brookes Point, Palawan	PHP	8.0	8	female	irregular
392	100.0	100.0	Home Energy	Personal Use	to buy a solar lamp.	SV	El Salvador	NaN	USD	14.0	4	male	monthly
405	100.0	100.0	Home Energy	Personal Use	to buy a solar-powered lamp.	SV	El Salvador	NaN	USD	14.0	4	male	monthly
498	100.0	100.0	Home Energy	Personal Use	to buy a solar-powered lamp.	SV	El Salvador	NaN	USD	14.0	4	male	monthly
606	100.0	100.0	Home Energy	Personal Use	to buy a solar-powered lamp.	SV	El Salvador	NaN	USD	14.0	4	male	monthly
...	...	...	...	...	...	...	...	...	...	...	...	...	...
671200	0.0	25.0	Livestock	Agriculture	[True, u'para compara: cemento, arenya y ladri...	PY	Paraguay	Concepción	USD	13.0	0	female	monthly
671201	25.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	1	female	monthly
671202	0.0	25.0	Games	Entertainment	NaN	KE	Kenya	NaN	KES	13.0	0	NaN	monthly
671203	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly
671204	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
47	250.0	250.0	Farming	Agriculture	to purchase potato seeds and fertilizers for g...	IN	India	Dhupguri	INR	43.0	10	female	bullet
154	1325.0	1325.0	Food Production/Sales	Food	to buy shea nuts.	ML	Mali	Kadiolo	XOF	7.0	50	female, female, female, female, female	monthly
230	4275.0	4275.0	Personal Housing Expenses	Housing	to pay for the construction of a hygienic toil...	VN	Vietnam	Hai Duong	VND	14.0	147	female, female, female, male, female, female, ...	bullet
237	4275.0	4275.0	Personal Housing Expenses	Housing	to pay for the construction of a hygienic toil...	VN	Vietnam	Hai Duong	VND	14.0	144	female, female, female, female, female, male, ...	bullet
314	4750.0	4750.0	Personal Housing Expenses	Housing	to pay for the construction of a hygienic toil...	VN	Vietnam	Hai Duong	VND	14.0	173	female, female, female, female, female, female...	bullet
...	...	...	...	...	...	...	...	...	...	...	...	...	...
671200	0.0	25.0	Livestock	Agriculture	[True, u'para compara: cemento, arenya y ladri...	PY	Paraguay	Concepción	USD	13.0	0	female	monthly
671201	25.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	1	female	monthly
671202	0.0	25.0	Games	Entertainment	NaN	KE	Kenya	NaN	KES	13.0	0	NaN	monthly
671203	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly
671204	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
27	475.0	475.0	Rickshaw	Transportation	to cover the cost of repairing rickshaw	PK	Pakistan	Lahore	PKR	12.0	17	female	irregular
47	250.0	250.0	Farming	Agriculture	to purchase potato seeds and fertilizers for g...	IN	India	Dhupguri	INR	43.0	10	female	bullet
142	4750.0	4750.0	Personal Housing Expenses	Housing	to pay for the construction of a hygienic toil...	VN	Vietnam	Hai Duong	VND	14.0	162	female, female, female, female, female, female...	bullet
145	1200.0	1200.0	Personal Expenses	Personal Use	NaN	PE	Peru	NaN	PEN	20.0	44	NaN	monthly
154	1325.0	1325.0	Food Production/Sales	Food	to buy shea nuts.	ML	Mali	Kadiolo	XOF	7.0	50	female, female, female, female, female	monthly
...	...	...	...	...	...	...	...	...	...	...	...	...	...
671200	0.0	25.0	Livestock	Agriculture	[True, u'para compara: cemento, arenya y ladri...	PY	Paraguay	Concepción	USD	13.0	0	female	monthly
671201	25.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	1	female	monthly
671202	0.0	25.0	Games	Entertainment	NaN	KE	Kenya	NaN	KES	13.0	0	NaN	monthly
671203	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly
671204	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly

	use	country	count_use
0	To buy chicken.	El Salvador	1
1	\t To buy more dresses, jeans, stoles, saris, ...	India	1
2	\t To purchase rice, lard, bread, soft drinks,...	Honduras	1
3	\t to purchase and sell sheets, towels, and be...	Ecuador	1
4	\tRose to buy farm implements.	Kenya	1
...	...	...	...
436576	‎to expand his kiosk with new coffee machine a...	Jordan	1
436577	‎to expand his shop with snacks, food supplies...	Jordan	1
436578	‎to help him to cover the rest of his semester...	Jordan	1
436579	“button down” things that need to legally be d...	United States	1
436580	” provide a safer and more secure living envir...	United States	1

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
140	2975.0	2975.0	Food Production/Sales	Food	NaN	TZ	Tanzania	NaN	TZS	10.0	110	NaN	monthly
145	1200.0	1200.0	Personal Expenses	Personal Use	NaN	PE	Peru	NaN	PEN	20.0	44	NaN	monthly
170	4250.0	4250.0	Catering	Food	NaN	TZ	Tanzania	NaN	TZS	10.0	116	NaN	monthly
412	2350.0	2350.0	Beauty Salon	Services	NaN	TZ	Tanzania	NaN	TZS	10.0	75	NaN	monthly
414	725.0	725.0	Agriculture	Agriculture	NaN	SV	El Salvador	NaN	USD	20.0	19	NaN	monthly
...	...	...	...	...	...	...	...	...	...	...	...	...	...
659604	5625.0	10000.0	Weaving	Arts	NaN	BT	Bhutan	NaN	USD	14.0	210	NaN	irregular
660788	1975.0	1975.0	Home Energy	Personal Use	NaN	PS	Palestine	NaN	USD	27.0	39	NaN	monthly
661718	800.0	1600.0	Furniture Making	Manufacturing	NaN	HT	Haiti	NaN	HTG	13.0	27	NaN	irregular
671151	0.0	25.0	Livestock	Agriculture	NaN	KE	Kenya	NaN	KES	13.0	0	NaN	monthly
671174	0.0	25.0	Games	Entertainment	NaN	KE	Kenya	NaN	KES	13.0	0	NaN	monthly

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
202537	4150.0	4150.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	EEnhana	NAD	6.0	162	female	bullet
202823	4150.0	4150.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	Rundu	NAD	6.0	159	male	bullet
344929	3325.0	3325.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	EEnhana	NAD	7.0	120	female	bullet
351177	3325.0	3325.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	Rundu	NAD	7.0	126	male	bullet
420953	3325.0	3325.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	EEnhana	NAD	7.0	118	female	bullet
421218	4000.0	4000.0	Wholesale	Wholesale	purchase solar lighting products for sale to l...	NaN	Namibia	Rundu	NAD	7.0	150	male	bullet
487207	5100.0	5100.0	Renewable Energy Products	Retail	to pay for stock of solar lights and cell phon...	NaN	Namibia	Katima Mulilo	NAD	7.0	183	male	bullet
487653	5000.0	5000.0	Wholesale	Wholesale	to maintain a stock of solar lights and cell p...	NaN	Namibia	Oshakati	NAD	7.0	183	female	bullet

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
49	450.0	450.0	General Store	Retail	to stock his store.	SV	El Salvador	NaN	USD	14.0	18	male	monthly
202	500.0	500.0	General Store	Retail	to buy beans, oil, sugar, coffee, sweets, soft...	SV	El Salvador	NaN	USD	14.0	12	female	monthly
211	550.0	550.0	Food Production/Sales	Food	to buy corn, firewood, and propane gas, and to...	SV	El Salvador	San Miguel, San Miguel	USD	14.0	20	female	monthly
215	500.0	500.0	Personal Housing Expenses	Housing	to buy cement, iron, and hurricane fencing	SV	El Salvador	NaN	USD	14.0	19	male	monthly
255	300.0	300.0	Fruits & Vegetables	Food	to buy fruit and vegetables wholesale like ora...	SV	El Salvador	NaN	USD	11.0	12	female	monthly
...	...	...	...	...	...	...	...	...	...	...	...	...	...
671088	0.0	300.0	Fruits & Vegetables	Food	to purchase a cart to sell fruit and she will ...	SV	El Salvador	Gotera	USD	14.0	0	female	monthly
671095	75.0	450.0	Agriculture	Agriculture	Rosa to buy agricultural inputs to grow basic ...	SV	El Salvador	San Miguel	USD	12.0	3	female	bullet
671096	0.0	325.0	Food Production/Sales	Food	to purchase a new industrial griddle to sell t...	SV	El Salvador	Gotera	USD	14.0	0	female	irregular
671097	500.0	500.0	Crafts	Arts	to buy thread to make hammocks.	SV	El Salvador	Gotera	USD	12.0	9	female	bullet
671122	0.0	1000.0	Internet Cafe	Services	to buy stationery goods, pencils, erasers and ...	SV	El Salvador	NaN	USD	20.0	0	female	monthly

	funded_amount	loan_amount	activity	sector	country_code	country	currency	term_in_months	lender_count	borrower_genders	repayment_interval
4814	0.0	5000.0	Food Production/Sales	Food	US	United States	USD	24.0	0	female	bullet
6784	0.0	10000.0	Cosmetics Sales	Retail	US	United States	USD	36.0	0	male	bullet
10735	0.0	5000.0	Services	Services	US	United States	USD	24.0	0	male	bullet
12037	0.0	5000.0	Food Production/Sales	Food	US	United States	USD	24.0	0	male	bullet
13542	0.0	1500.0	Crafts	Arts	US	United States	USD	12.0	0	male	bullet
...	...	...	...	...	...	...	...	...	...	...	...
671179	0.0	25.0	Livestock	Agriculture	PY	Paraguay	USD	13.0	0	female	monthly
671181	0.0	25.0	Livestock	Agriculture	PK	Pakistan	PKR	13.0	0	female	monthly
671182	0.0	125.0	Livestock	Agriculture	MX	Mexico	MXN	13.0	0	female, female	monthly
671184	0.0	875.0	Livestock	Agriculture	BO	Bolivia	BOB	13.0	0	female, female	monthly
671188	0.0	250.0	Livestock	Agriculture	GH	Ghana	GHS	13.0	0	female	monthly

	country	Alpha-3_code
0	Afghanistan	AFG
1	Åland Islands	ALA
2	Albania	ALB
3	Algeria	DZA
4	American Samoa	ASM
...	...	...
241	Wallis and Futuna	WLF
242	Western Sahara	ESH
243	Yemen	YEM
244	Zambia	ZMB
245	Zimbabwe	ZWE

	country	projects_count	funded_amount_sum	loan_amount_sum
59	Philippines	154309	53171450.0	54036750.0
34	Kenya	73525	31805605.0	34078100.0
22	El Salvador	39845	23345975.0	26590850.0
11	Cambodia	26957	17686650.0	18738225.0
54	Pakistan	26453	12332500.0	13157625.0
...	...	...	...	...
83	Virgin Islands	2	0.0	10000.0
0	Afghanistan	2	14000.0	14000.0
18	Cote D'Ivoire	1	50000.0	50000.0
25	Guam	1	395.0	4300.0
44	Mauritania	1	15000.0	15000.0

	activity	sector	country_code	country	currency	borrower_genders	repayment_interval
count	646617	646617	646617	646617	646617	646617	646617
unique	163	15	87	87	67	11298	4
top	Farming	Agriculture	PH	Philippines	PHP	female	monthly
freq	69479	174578	154309	154309	154308	418768	330282

	country	gender	activity	sector	projects_count	funded_amount_sum	loan_amount_sum	lender_count_sum
0	Afghanistan	female	Textiles	Arts	2	14000.0	14000.0	512
1	Albania	female	Agriculture	Agriculture	86	105900.0	107475.0	3554
2	Albania	female	Animal Sales	Agriculture	3	5275.0	5325.0	187
3	Albania	female	Bakery	Food	2	4950.0	4950.0	180
4	Albania	female	Barber Shop	Services	1	1500.0	1650.0	47
...	...	...	...	...	...	...	...	...
12377	Zimbabwe	multiple female	Used Clothing	Clothing	23	28125.0	29200.0	837
12378	Zimbabwe	multiple female	Used Shoes	Clothing	1	1100.0	1100.0	38
12379	Zimbabwe	multiple female	Vehicle Repairs	Services	1	1300.0	1300.0	27
12380	Zimbabwe	multiple female	Wholesale	Wholesale	7	9300.0	9300.0	297
12381	Zimbabwe	multiple male	Used Clothing	Clothing	1	2150.0	2150.0	71

	country	Alpha-3_code	sector	projects_count	funded_amount_sum	loan_amount_sum	lender_count_sum	rate_of_success
377	Kenya	KEN	Agriculture	32874	16286110.0	17410825.0	497143	94.0
378	Kenya	KEN	Arts	342	104800.0	105575.0	4840	99.0
379	Kenya	KEN	Clothing	4661	1525050.0	1645800.0	55776	93.0
380	Kenya	KEN	Construction	797	305765.0	309650.0	10270	99.0
381	Kenya	KEN	Education	1218	864475.0	874525.0	27678	99.0
382	Kenya	KEN	Entertainment	31	14450.0	14500.0	501	100.0
383	Kenya	KEN	Food	13751	4523320.0	4817475.0	145333	94.0
384	Kenya	KEN	Health	630	681200.0	681875.0	23299	100.0
385	Kenya	KEN	Housing	286	130550.0	136375.0	3887	96.0
386	Kenya	KEN	Manufacturing	295	232325.0	234650.0	6789	99.0
387	Kenya	KEN	Personal Use	1296	302700.0	305325.0	9646	99.0
388	Kenya	KEN	Retail	10092	4210965.0	4644650.0	132815	91.0
389	Kenya	KEN	Services	5408	1884710.0	2022025.0	70511	93.0
390	Kenya	KEN	Transportation	1823	725435.0	861100.0	22629	84.0
391	Kenya	KEN	Wholesale	21	13750.0	13750.0	484	100.0
920	United States	USA	Agriculture	403	2566375.0	2999050.0	53248	86.0
921	United States	USA	Arts	287	1069655.0	1351500.0	20520	79.0
922	United States	USA	Clothing	420	1393120.0	1957925.0	23983	71.0
923	United States	USA	Construction	95	304700.0	435900.0	5999	70.0
924	United States	USA	Education	178	646520.0	870125.0	13558	74.0
925	United States	USA	Entertainment	183	419695.0	776725.0	7522	54.0
926	United States	USA	Food	1200	5850715.0	7208975.0	101794	81.0
927	United States	USA	Health	75	315225.0	396450.0	6669	80.0
928	United States	USA	Housing	46	173650.0	242850.0	3653	72.0
929	United States	USA	Manufacturing	39	163900.0	213300.0	3447	77.0
930	United States	USA	Personal Use	78	430375.0	481775.0	9507	89.0
931	United States	USA	Retail	925	2999520.0	4437450.0	51732	68.0
932	United States	USA	Services	2010	6311925.0	9420300.0	122726	67.0
933	United States	USA	Transportation	132	422640.0	597925.0	8231	71.0
934	United States	USA	Wholesale	21	90525.0	105125.0	1717	86.0

	country	Alpha-3_code	sector	projects_count	funded_amount_sum	loan_amount_sum	lender_count_sum	rate_of_success
1	Albania	ALB	Agriculture	719	976925.0	1005350.0	32627	97.0
16	Armenia	ARM	Agriculture	4629	6605975.0	7586075.0	181393	87.0
31	Azerbaijan	AZE	Agriculture	1145	1554025.0	1661725.0	47857	94.0
45	Belize	BLZ	Agriculture	124	113500.0	113500.0	4164	100.0
46	Benin	BEN	Agriculture	2	50375.0	50375.0	1529	100.0
...	...	...	...	...	...	...	...	...
920	United States	USA	Agriculture	403	2566375.0	2999050.0	53248	86.0
936	Vietnam	NaN	Agriculture	2610	4710075.0	5139600.0	138276	92.0
953	Yemen	YEM	Agriculture	160	75700.0	75700.0	2304	100.0
967	Zambia	ZMB	Agriculture	106	90250.0	99875.0	3060	90.0
977	Zimbabwe	ZWE	Agriculture	1050	813950.0	817700.0	23946	100.0

	country	Alpha-3_code	gender	activity	sector	projects_count	funded_amount_sum	loan_amount_sum	lender_count_sum	rate_of_success
14	Albania	ALB	female	Consumer Goods	Personal Use	1	700.0	2025.0	28	35.0
26	Albania	ALB	female	Food	Food	2	1150.0	2550.0	43	45.0
87	Albania	ALB	male	Electrical Goods	Retail	1	750.0	1650.0	29	45.0
101	Albania	ALB	male	General Store	Retail	3	2725.0	6100.0	101	45.0
231	Armenia	ARM	female	Veterinary Sales	Agriculture	1	75.0	1050.0	3	7.0
...	...	...	...	...	...	...	...	...	...	...
11966	Virgin Islands	NaN	female	General Store	Retail	1	0.0	5000.0	0	0.0
11967	Virgin Islands	NaN	female	Services	Services	1	0.0	5000.0	0	0.0
12018	Yemen	YEM	male	Computer	Education	1	0.0	1050.0	0	0.0
12055	Yemen	YEM	male	Office Supplies	Retail	1	375.0	950.0	14	39.0
12163	Zambia	ZMB	male	Livestock	Agriculture	1	375.0	10000.0	15	4.0

Abschlussprojekt - Data Analytics¶

1. Teil - Datenanalyse¶

CRISP DM: Business Understanding¶

CRISP DM: Data Understanding¶

2. Teil - Dashboard¶

Step 1 Data Preparation¶

Step 2: Daten kennenlernen¶

Step 3 Umgang mit Dubletten / Analyse & Begründung:¶

Löschen der Duplikate¶

Step 4: Fehlende Werte - AKTUALISIEREN!!!!!!!!!!!!!!!!!!¶

Überblick der systemseitig erkannten fehlenden Werte:¶

Spalte: Region - ausführliche Gedanken und Begründung¶

Spalte: Spalte borrower_genders - ausführliche Gedanken und Begründung¶

Ersetzen der NaN Werte in der Spalte borrow_genders¶

Spalte: Use - ausführliche Gedanken und Begründung¶

Spalte: country_code - ausführliche Gedanken und Begründung¶

Prüfen auf nicht systemseitige Nullwerte¶

Step 5: Ausreißer¶

Beurteilung Ausreißer¶

Step 7: Ergänzung der Rohdaten um berechnete Kennzahlen¶

Neue Spalte Gender¶

Neue Spalte Alpha-3_code¶

Neue Spalte success rate (KPI)¶

Data Frame für EDA¶

Explorative Datenanalyse¶

Aufgabenstellung¶

Business Fragestellung - Annahmen und Sollzustand definieren¶

Daten selektieren, gruppieren, aggregieren¶

Plots & Muster finden¶

Plot 1 zur Bewertung¶

Anzahl an Kredit Anfragen nach Land - zur Bewertung¶

Insights - Anzahl an Kredit Anfragen nach Land - zur Bewertung¶

Zielbeträge der Kredite pro Land gesamt¶

Insights - Zielbeträge der Kredite pro Land gesamt¶

Success Rate nach Land¶

Insights Success Rate nach Land¶

Plot 2 - zur Bewertung¶

Success Rate nach Land - zur Bewertung¶

Insights - Success Rate nach Land - zur Bewertung¶

Gender Abhängigkeit der Projekt Owner zu den erhaltenen Kredit Beträgen¶

Insights - Gender Abhängigkeit der Projekt Owner zu den erhaltenen Kredit Beträgen¶

Plot 3 - zur Bewertung¶

Erhaltener Kreditbetrag nach Geschlecht, Sector und farbliche Darstellung der Success Rate - zur Bewertung¶

Insight - Anzahl Projekte nach Sector, Geschlecht und Success-Rate - zur Bewertung¶

Dashboard¶

Weitere tiefere Analyse¶

Anzahl Projekte nach Activity, Geschlecht und Success-Rate¶

Insights - Anzahl Projekte nach Activity, Geschlecht und Success-Rate¶

Anzahl der Projekte nach Sector und Länder und Success Rate¶

Insights - Anzahl der Projekte nach Sector und Länder und Success Rate¶

Einzelfallbetrachtung United States¶

Insight - Einzelfallbetrachtung United States¶¶

Scatter Matrix¶

Success Rate < 50 % / Sector / Land¶

Insight - Success Rate < 50 % / Sector / Land¶

Rückzahlungsrate nach Geschlecht und Success Rate¶